#include <admin_function.h>

//链接sql
int exec_sql(sqlite3 * db,const char *sql){
    
                char *err_msg =NULL;
		int res =sqlite3_exec(db,sql,NULL,NULL,&err_msg);
		if(res !=SQLITE_OK){
		  fprintf(stderr,"sql链接错误:%s\n",err_msg);
			  sqlite3_free(err_msg);
			  return 0;
		}
		return 1;
    
    }
//创建用户表
void init_db(sqlite3 *db){
            

	      exec_sql(db,"create table if not exists user ("
			   "user_id integer primary key autoincrement,"
	                   "username text not null unique,"
			   "email text not null unique,"
			   "password text not null,"
			   "role_id integer not null,"
			   "status text default 'active',"
			   "foreign key (role_id) REFERENCES role(role_id))");
//创建角色

             exec_sql(db,"create table if not exists role ("
	     "role_id integer primary key autoincrement,"
	     "role_name text not null unique,"
	     "role_desc text)");

	     const  char *check ="select count(*) from role";
	     sqlite3_stmt *stmt;
	        if(sqlite3_prepare_v2(db,check,-1,&stmt,NULL)==SQLITE_OK){
		   if(sqlite3_step(stmt) == SQLITE_ROW && sqlite3_column_int(stmt,0)==0){
		      //插入角色
		      exec_sql(db, "INSERT INTO role(role_id, role_name, role_desc) VALUES "
				    "(1, '不会篮球', 'user'),"
				    "(2, '喜欢唱跳', 'user'),"
				    "(3, '爱坤一号', 'admin')");
		      exec_sql(db,"insert into user(username, email, password, role_id)"
				      "VALUES('admin', 'admin@test.com', 'admin123', 3)");
		   }
		   sqlite3_finalize(stmt);
		}
			      
	   }

                //显示用户列表
void show_users(sqlite3 *db){
		printf("\n用户列表:\n");
		const char *sql ="SELECT  * "
			"FROM users;";
		   sqlite3_stmt *stmt;
		   if(sqlite3_prepare_v2(db,sql,-1,&stmt,NULL)==SQLITE_OK){
		       printf("id   user_name  password\n");
		       printf("-------------------------------------------\n");
		       while(sqlite3_step(stmt)==SQLITE_ROW){
		        int id = sqlite3_column_int(stmt,0);
			   const unsigned char *user_name =sqlite3_column_text(stmt,1);
			   int password = sqlite3_column_int(stmt,2);
			    printf("%d     %s    %d\n",id,user_name,password);
		       }
		       sqlite3_finalize(stmt);
		   }else{
		     fprintf(stderr,"查询用户失败:%s\n",sqlite3_errmsg(db));
		   }
		}
